package com.demo.core.orm.ibatis;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.demo.core.dao.base.support.ResultSearchResult;
import com.demo.core.dao.base.support.SearchArgumentAware;
import com.demo.core.dao.base.support.SearchArgumentSupport;
import com.demo.core.model.support.GeneralExecuteModel;

import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate;
import com.ibatis.sqlmap.engine.mapping.sql.Sql;
import com.ibatis.sqlmap.engine.mapping.sql.dynamic.DynamicSql;
import com.ibatis.sqlmap.engine.mapping.sql.simple.SimpleDynamicSql;
import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement;
import com.ibatis.sqlmap.engine.scope.SessionScope;
import com.ibatis.sqlmap.engine.scope.StatementScope;
import com.ibatis.sqlmap.engine.transaction.Transaction;
import com.ibatis.sqlmap.engine.transaction.TransactionException;
/**
 * 带有特征化的SqlMapExecutorDelegate
 * 该类方便执行带有分页的请求，并将分页的实现进行隔离
 * 该类需要GeneralExecuteSql.xml的支持，如果不适用该配置文件，
 * 则需要手动调整SQL的执行情况
 * 
 * @author ryuu.kk
 *
 */
public class DialectSqlMapExecutorDelegate extends SqlMapExecutorDelegate {
	/**
	 * 直接执行SQL脚本语句de对应名称(SqlMap的配置名)
	 */
	protected static final String DIALECT_EXECUTE_NAME = "GeneralExecuteSpace.dataCount";
	/**
	 * Log日志
	 */
	protected static final Log log = LogFactory.getLog(DialectSqlMapExecutorDelegate.class);
	  /**
	   * Execute a query for a Object
	   * If paramObject contents a SearchArgumentSupport, return ResultSearchResult Object
	   * @param sessionScope - the session scope
	   * @param id          - the statement ID
	   * @param paramObject - the parameter object
	   * @param skip        - the number of rows to skip
	   * @param max         - the maximum number of rows to return
	   * @return - the data list
	   * @throws SQLException - if the query fails
	   */
	@SuppressWarnings("unchecked")
	public Object queryForObject(SessionScope sessionScope, String id, Object paramObject, Object resultObject) throws SQLException {
		//结果
	    List<Object> list = null;
	    Object obj = null;
	    int maxResults = -1;
	    boolean isPager = isQueryPager(paramObject);
	    MappedStatement ms = getMappedStatement(id);
	    Transaction trans = getTransaction(sessionScope);
	    boolean autoStart = trans == null;
	    
	    try {
			trans = autoStartTransaction(sessionScope, autoStart, trans);
			StatementScope statementScope = beginStatementScope(sessionScope, ms);

			// ~~~~~~~~~~~~~~~~~~~ 测试 ~~~~~~~~~~~~~~~~~~~~~~~~~
			// 将转换为代理类处理
			try {
				if (isPager) {
					int skip = SqlExecutor.NO_SKIPPED_RESULTS; 
					int max = SqlExecutor.NO_MAXIMUM_RESULTS;
					String primitiveSql = evolutionDynamicSql(ms, statementScope, paramObject);
					list = ms.executeQueryForList(statementScope, trans, paramObject, skip, max);
					maxResults = queryMaxData(ms, statementScope, trans, primitiveSql, paramObject);
				} else {
					obj = ms.executeQueryForObject(statementScope, trans, paramObject, resultObject);
				}
			} finally {
				endStatementScope(statementScope);
			}
			//~~~~~~~~~~~~~~~~~~~  测试   ~~~~~~~~~~~~~~~~~~~~~~~~~
	      autoCommitTransaction(sessionScope, autoStart);
	    } finally {
	      autoEndTransaction(sessionScope, autoStart);
	    }
	    
	    return isPager ? new ResultSearchResult<Object>(list, maxResults) : obj;
	  }
	
	@SuppressWarnings("unchecked")
	private boolean isQueryPager(Object paramObject) {
		if (paramObject != null && paramObject instanceof Map) {
			Map<String, Object> paramMap = ((Map<String, Object>) paramObject);
			String SearchArgsKey = SearchArgumentSupport.class.getName();
			return paramMap.containsKey(SearchArgsKey);
		}
		
		if (paramObject != null && paramObject instanceof SearchArgumentAware) {
			SearchArgumentAware argAware = ((SearchArgumentAware) paramObject);
			return argAware.getPageSupport() != null;
		}
		
		return false;
	}
	
	/**
	 * 演进动态的SQL方法
	 * 该方法将原始SQL做处理,进行包装后,生成带有分页的动态SQL
	 * @param ms 				执行MAP
	 * @param statementScope 	State Scope
	 * @param paramMap			参数对象
	 * @return 原始的SQL
	 */
	private String evolutionDynamicSql(MappedStatement ms, StatementScope statementScope, Object paramObject) {
	    int firstResults = SqlExecutor.NO_SKIPPED_RESULTS;
	    int maxResults = SqlExecutor.NO_MAXIMUM_RESULTS;
	    //原始的SQL语句,如果使用SQL不能通过getDynamicSql()取得。
	    String primitiveSql = "";
	    // 取得分页参数模型对象
		SearchArgumentSupport searchArgs = getSearchArgumnetSupport(paramObject);
		//首条记录位置
		firstResults = searchArgs.getFirstResult();
		//最大检索条数
		maxResults = searchArgs.getMaxResults();
		String sql = statementScope.getDynamicSql();
		//
		Sql sqlModel = ms.getSql();
		if (sql == null) {
			sql = sqlModel.getSql(statementScope, paramObject);
		}
		
		// --------------------------------------------------------------------------------
		// |  增加自动排序功能          at 2012-07-02 by ryuu.kk
		// |  排序满足在不修改原始Sql的前提下,保证排序后分页和分页后排序功能
		// |  该做法有一个隐含的问题就是ORDER BY 不能在SQL中出现,比如一个不规范的SQL语句将
		// |  导致SQL错误: SELECT * FROM (SELECT * FROM T_CORE_USER GROUP BY ORG_ID ORDER BY ID) WHERE ....
		// |  该ORDER BY 在嵌套语句中出现,而同时最后并没有ORDER BY 字段,本方法将出现问题
		// |  同时ORDER 与 BY之间至多一个空格
		// --------------------------------------------------------------------------------
		String[] orders = searchArgs.getOrders();
		String orderBy = " ORDER BY ";
		if (orders != null && orders.length > 0) {
			// 存在排序字段时
			for(String order : orders) {
				orderBy += order;
			}
			int orderIndex = sql.toUpperCase().lastIndexOf("ORDER BY");
			int lastBarcket = sql.toUpperCase().lastIndexOf(")");
			// 判断SQL嵌套
			if (orderIndex == -1) {
				// SQL不存在ORDER BY字段
				sql += orderBy;
			} 
			if ( orderIndex > lastBarcket) {
				// 本身SQL语句存在Order排序字段,并且Order在SQL最后
				sql = sql.substring(0, orderIndex) + orderBy;
			}
		}
		
		primitiveSql = sql;
		//数据库类型代理,不同数据库将拼接不同Sql....
		//String sqlForamt = "%s LIMIT %d, %d";
		//statementScope.setDynamicSql(String.format(sqlForamt, sql, firstResults, firstResults + maxResults));
		String dbName = getDbName(statementScope);
		try {
			//进化其原始SQL语句
			sql = dbScriptFormat(dbName, sql, firstResults, maxResults);
			statementScope.setDynamicSql(sql);
			
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		// --------------------------------------------------------------------------------
		// |  $语法的Sql生成处理          at 2013-02-01 by ryuu.kk
		// |  有$model.exeSql$动态Sql时,分页SQL没有生成,原因是SimpleDynamicSql处理Sql时
		// |  无视statementScope的参数Sql,使用Sql对象生成新Sql脚本
		// |  @see SimpleDynamicSql#getSql
		// --------------------------------------------------------------------------------
		if (sqlModel instanceof SimpleDynamicSql) {
			// 分页时出现的SimpleDynamicSql($xxx$),重新定义它,并使用新语句替换原有Sql语句
			SimpleDynamicSql dySql = new SimpleDynamicSql(this, primitiveSql);
			//ms.setSql(dySql);
			statementScope.setSql(dySql);
		}
		return primitiveSql;
	}
	
	private Integer queryMaxData(MappedStatement archetypeMs, StatementScope statementScope, Transaction trans, String primitiveSql, Object paramObject) throws SQLException {
		//原始Sql
		String archetypeSql = primitiveSql;
		/* BUG: 分页会改变原始SQL语句。
		String archetypeSql = statementScope.getDynamicSql();
		if (archetypeSql == null) {
			//不存在,使用SqlMap配置生成
			archetypeSql = archetypeMs.getSql().getSql(statementScope, paramMap);
		}
		*/
		/*******************  使用GeneralExecuteSql.xml文件的配置信息进行分页最大条数查询   ************************ /
		 * 																									 *  
		 * 			这里将动态的构建执行参数，将该执行参数加入到原始请求参数中。									 *
		 *			因为改变了SQL检索的列（只检索COUNT(*)）		 												 *
		 *			1、原始sqlMap的resultMap(或resultObject)无效												 *
		 *			2、原始sqlMap的parameterMap(或parameterClass)有效											 *
		 *			3、需要进化后sqlMap的resultMap(可以为空)													 *
		 *			P.S.:如果不使用GeneralExecuteSql文件作为默认执行,可以动态构建执行SQL,需要使用原始请求参数,		 *
		 *				 将结果参数设置为空																	 *
		 *																									 *
		 *****************************************************************************************************/
		//进化后的SqlMap
		MappedStatement evolutionMs = getMappedStatement(DIALECT_EXECUTE_NAME);
		StatementScope evolutionScope = beginStatementScope(statementScope.getSession(), evolutionMs);
		
		GeneralExecuteModel<?> model = new GeneralExecuteModel<java.io.Serializable>();
		model.setTable(archetypeSql);
		model.setPk("*");
		// 设置总页数的模型
		addGeneralExecuteModel(paramObject, model);
		//进化Sql
		String evolutionSql = evolutionScope.getDynamicSql();
		if (evolutionSql == null) {
			evolutionSql = evolutionMs.getSql().getSql(evolutionScope, paramObject);
		}
		/*----------------------------------不适用默认XML配置文件,手动操作----------------------------------------*/
		//String sqlForamt = "SELECT COUNT(*) FROM (%s) T";
		//statementScope.setDynamicSql(String.format(sqlForamt, sql));
		//statementScope.setResultMap(null);
		/*----------------------------------------------------------------------------------------------------*/

		//使用进化后的Sql脚本(给TABLE加一个别名T : Mysql需要)
		/**/
		String oldSql = statementScope.getDynamicSql();
		if (oldSql == null) {
			oldSql = statementScope.getSql().getSql(statementScope, paramObject);
		}
		statementScope.setDynamicSql(evolutionSql + " T");
		//使用进化后的结果集映射
		statementScope.setResultMap(evolutionScope.getResultMap());
		//因为检索条件将有不确定参数，则使用原始参数对象。使用进化后的Sql脚本和结果集
		Object obj = evolutionMs.executeQueryForObject(statementScope, trans, paramObject, evolutionScope.getResultMap());
		//statementScope.setDynamicSql(oldSql);
		
		return obj instanceof Integer ? (Integer) obj : 0;
	}
	
	private String dbScriptFormat(String dbName, String sql, int offset, int limit) throws Exception {
		String sqlScript = "";
		if (dbName.toLowerCase().indexOf("mysql") != -1) {
			String sqlForamt = "%s LIMIT %d, %d";
			sqlScript = String.format(sqlForamt, sql, offset, limit);
		}
		if (dbName.toLowerCase().indexOf("microsoft sql server") != -1) {
			throw new Exception("目前不支持sqlServer");
		}
		if (dbName.toLowerCase().indexOf("oracle") != -1) {
			String sqlForamt = "";
			if (offset > 0) {
				sqlForamt = "SELECT T.* FROM (SELECT T.* " +
				            " FROM (SELECT T.*, rownum rowindex " +
				            "          FROM (%s) T) T " +
				            "WHERE T.rowindex <= %d) T WHERE T.rowindex > %d ";
				sqlScript = String.format(sqlForamt, sql, offset + limit, offset);
			} else {
				sqlForamt = "SELECT * FROM (%s) WHERE rownum <=%d";
				sqlScript = String.format(sqlForamt, sql, limit);
			}
		}
		if (dbName.toLowerCase().indexOf("db2") != -1) {
			throw new Exception("目前不支持DB2");
		}

		return sqlScript;
	}
	
	private String getDbName(StatementScope statementScope) {
		String dbName = "unknow";
		if (statementScope != null) {
			Transaction trans = statementScope.getSession().getTransaction();
			try {
				dbName = trans.getConnection().getMetaData().getDatabaseProductName();
			} catch (SQLException e) {
				log.error(e.getMessage());
			} catch (TransactionException e) {
				log.error(e.getMessage());
			}
		}
		return dbName;
	}
	
	/**
	 * 取得分页参数
	 * @param obj 检索参数
	 * @return SearchArgumentSupport 分页参数模型
	 */
	private SearchArgumentSupport getSearchArgumnetSupport(Object obj) {
		
		//取得分页参数
	    String searchArgsKey = SearchArgumentSupport.class.getName();
		if (obj instanceof Map) {
			return (SearchArgumentSupport) ((Map<?,?>) obj).get(searchArgsKey);
		}
		
		if (obj instanceof SearchArgumentAware) {
			return ((SearchArgumentAware) obj).getPageSupport();
		}
		
		//throws something?
		return null;
	}

	/**
	 * 总页长的执行模型加入到参数模型中
	 * @param paramObject 参数模型
	 * @param model 总页长的参数模型
	 */
	@SuppressWarnings("unchecked")
	private void addGeneralExecuteModel(Object paramObject, GeneralExecuteModel<?> model) {
		
		if (paramObject instanceof Map) {
			((Map<String, Object>) paramObject).put("model", model);
		}
		
		if (paramObject instanceof SearchArgumentAware) {
			((SearchArgumentAware) paramObject).setModel(model);
		}
	}
}